# Contains methods needed for the indexing process
# run printConceptClassQueries first (already done on rpa machine)
# import the sql file generated ('doc/snomed/concept_classes.sql') into the 'snomed' database (already done on rpa machine)
# then run printTermDictionary (already done on rpa machine)
# import the sql file generated ('doc/snomed/concept_term.sql') into the 'snomed' database (already done on rpa machine)
# [NEEDS TO BE DONE ON RPA MACHINE] call run() to begin the main indexing process
# [NEEDS TO BE DONE ON RPA MACHINE] import the sql file genterated ('snomed/queries/sql_[start_pid]-[end_pid].sql') into the 'patient_concept' database

from SyntaxParser import buildFindingsHash
import MySQLdb
import conn
import odbc

class indexer:
	# Indexer initiator
	def __init__(self, start_pid, end_pid):
		self.start_pid = start_pid
		self.end_pid = end_pid
	
	# Connect to database	
	def sql_connect(self, database):
	    try:
	        ## You may have to change the username and password.
	        ##
	        connection = MySQLdb.connect(host="localhost",
	                                     user="root", passwd="notroot",port=3406,
	                                     db=database )
	        return connection
	
	    except MySQLdb.OperationalError, message:
	        errorMessage = "Error %d:\n%s" % (message[ 0 ], message[ 1 ] )
	        print errorMessage
	
	# Print out the concept_class queries	    
	def printConceptClassQueries(self):
	    output = open('doc/snomed/concept_classes.sql', 'w')
		
	    connection = self.sql_connect("snomed")
		
	    hierarchy_mapping_path = "doc/snomed/hierarchy_mapping.txt"
	    hierarchy_mapping = buildFindingsHash(hierarchy_mapping_path)
		
	    query_getConcepts = "SELECT conceptid, fullyspecifiedname FROM sct_concepts_20070731 ORDER BY conceptid"
	    cursor_getConcepts = connection.cursor()
	    cursor_getConcepts.execute(query_getConcepts)
	    for concept_id, fsn in cursor_getConcepts.fetchall():
	    	hierarchy_found = 0
	    	for hierarchy in hierarchy_mapping.keys():
	    		if fsn.find(hierarchy) != -1:
	    			output.write("INSERT INTO concept_classes VALUES (" + str(concept_id) + ", " + str(hierarchy_mapping.get(hierarchy)) + ");\n")
	    			hierarchy_found = 1
	    			break
	    	if hierarchy_found == 0:
	    		output.write("INSERT INTO concept_classes VALUES (" + str(concept_id) + ", 14);\n")
	    
	    output.close()
	    cursor_getConcepts.close()
	
	# Print out the term dictionary file in the format of [CONCEPT_ID]: SYNONYM|PREFERRED TERM; SYNONYM|PREFERRED TERM;...        
	def printTermDictionary(self):
		connection = self.sql_connect("snomed")
		output = open('doc/snomed/concept_term.sql', 'w')
		
		#getting all concept_ids in clinical findings
		query_main = "SELECT c.conceptid FROM sct_concepts_20070731 c, concept_classes cc WHERE cc.concept_class = 2 AND c.conceptid = cc.concept_id AND c.conceptid > 30884007 ORDER BY c.conceptid"
		cursor_main = connection.cursor()
		cursor_main.execute(query_main)
		for concept_id in cursor_main.fetchall():
		    line = ''
			
		    #adding concept_id
		    line = line + str(concept_id[0]) + ": "
		    
		    #adding preferred terms
		    query_getTerms = "SELECT DISTINCT term FROM sct_descriptions_20070731 WHERE (descriptiontype = 1 OR descriptiontype = 2) AND conceptid = " + str(concept_id[0])
		    cursor_getTerms = connection.cursor()
		    cursor_getTerms.execute(query_getTerms)
		    for term in cursor_getTerms.fetchall():
		    	output.write("INSERT INTO concept_term (concept_id, term) VALUES (" + str(concept_id[0]) + ", \"" + str(term[0]) + "\");\n")
		    cursor_getTerms.close()
		
		output.close()    
		cursor_main.close()

	# Print out the clinic notes XML file
	def printClinNoteXML(self):
		output = open('snomed/clinnotes/notes_' + str(self.start_pid) + '-' + str(self.end_pid) + '.xml', 'w')
		
		query = "SELECT pid, notetext FROM noteevents WHERE pid >= " + str(self.start_pid) + " AND pid <= " + str(self.end_pid) + " ORDER BY pid"                                                                    

		ism_db = "ism/ism/ism4u"
		db = None
		c = None
		r = []
		db = odbc.odbc(ism_db)
		c = db.cursor()
		c.execute(query)
		result = c.fetchall()
		c.close()
		for n in range(0, len(result)):
		    output.write("<note>\n")
		    output.write("<pid>" + str(result[n][0]) + "</pid>\n")
		    output.write("<notetext>" + str(result[n][1]) + "</notetext>\n")
		    output.write("</note>\n")
		    
		output.close()
	"""
	def printPrevClinNoteXML(self):
	
		query = "SELECT ne.pid, ne.notetext FROM noteevents ne, (SELECT MAX(intime) AS maxintime, MAX(outtime) AS maxouttime, pid FROM censusevents GROUP BY pid) ce WHERE ne.pid >= " + str(self.start_pid) + " AND ne.pid <= " + str(self.end_pid) + " AND (ne.chartTime < ce.maxintime OR ce.maxouttime > ce.maxintime) AND ne.pid = ce.pid ORDER BY ne.pid"                                                                    

		ism_db = "ism/ism/ism4u"
		#gicu_db = "gicu/hps/notroot"
		db = None
		c = None
		r = []
		db = odbc.odbc(ism_db)
		c = db.cursor()
		c.execute(query)
		result = c.fetchall()
		c.close()
		for n in range(0, len(result)):
		    print "<note>"
		    print "<pid>" + str(result[n][0]) + "</pid>"
		    print "<notetext>" + str(result[n][1]) + "</notetext>"
		    print "</note>"
		    
	
	def printCurrClinNoteXML(self):
	
		query = "SELECT ne.pid, ne.notetext FROM noteevents ne, (SELECT MAX(intime) AS maxintime, MAX(outtime) AS maxouttime, pid FROM censusevents GROUP BY pid) ce WHERE ne.pid >= " + str(self.start_pid) + " AND ne.pid <= " + str(self.end_pid) + " AND ne.chartTime >= ce.maxintime AND ce.maxintime > ce.maxouttime AND ne.pid = ce.pid ORDER BY ne.pid"                                                                     

		ism_db = "ism/ism/ism4u"
		#gicu_db = "gicu/hps/notroot"
		db = None
		c = None
		r = []
		db = odbc.odbc(ism_db)
		c = db.cursor()
		c.execute(query)
		result = c.fetchall()
		c.close()
		for n in range(0, len(result)):
		    print "<note>"
		    print "<pid>" + str(result[n][0]) + "</pid>"
		    print "<notetext>" + str(result[n][1]) + "</notetext>"
		    print "</note>"
	"""
	
	# Print out the indexing queries in file 'snomed/queries/sql_[start_pid]-[end_pic].sql'
	def printIndexingQueries(self):
		output = open('snomed/queries/sql_' + str(self.start_pid) + "-" + str(self.end_pid) + '.sql', 'w')
		
		connection = self.sql_connect("snomed")
		
		findings_terms_path = "doc/snomed/findings.txt"
		xml_path = "snomed/clinnotes/notes_" + str(self.start_pid) + "-" + str(self.end_pid) + ".xml"
		
		findings = buildFindingsHash(findings_terms_path)

		try:
		    input = open(xml_path,'r')
		    raw_xml = input.read()
		    
		    note_start_index = 0
		    note_end_index = 0
		    
		    current_pid = 0
		    current_findings = []
		    while 1:
				note_start_index = raw_xml.find("<note>", note_end_index)
				if note_start_index == -1:
					break;
					
				note_end_index = raw_xml.find("</note>", note_start_index)
				note = raw_xml[note_start_index:note_end_index].strip()
				
				pid_start_index = note.find("<pid>") + len("<pid>")
				pid_end_index = note.find("</pid>")
				pid = note[pid_start_index:pid_end_index].strip()
				
				notetext_start_index = note.find("<notetext>") + len("<notetext>")
				notetext_end_index = note.find("</notetext>")
				notetext = note[notetext_start_index:notetext_end_index].strip()
				
				""" for testing text-to-snomed
				tokens = toSnomed(notetext)
				for token in tokens:
					if token[2] == 2:
						try:
							findings.values().index(token[1][0])
							print str(pid) + ": " + str(token[1][0])
						except ValueError:
							continue
				
				"""
				
				query_getTerms = "SELECT term FROM concept_term"
				cursor_getTerms = connection.cursor()
				cursor_getTerms.execute(query_getTerms)
				terms = cursor_getTerms.fetchall()
				cursor_getTerms.close()
				
				for term in terms: #loop through every findings in snomed (including preferred terms and synonyms)
					term = str(term[0])
					finding_index = notetext.find(term)
					if finding_index != -1: #found keyword
					    query_getConceptIds = "SELECT concept_id FROM concept_term WHERE term = \"" + term + "\""
                                            cursor_getConceptIds = connection.cursor()
                                            cursor_getConceptIds.execute(query_getConceptIds)
                                            for concept_id in cursor_getConceptIds.fetchall():
                                                concept_id = concept_id[0]
                                                insert_eligible = 0
                                                
                                                if pid == current_pid:
                                                        try:
                                                                current_findings.index(concept_id)
                                                        except ValueError:
                                                                current_findings.append(concept_id)
                                                                insert_eligible = 1
                                                else:
                                                        current_pid = pid
                                                        current_findings = [concept_id]
                                                        insert_eligible = 1
                                                
                                                if insert_eligible == 1:
                                                        query = "INSERT INTO patient_concept (pid, concept_id) VALUES (" + str(pid) + ", " + str(concept_id) + ");"
                                                        output.write(query + '\n')
		    output.close()
							
		except IOError, (errno, strerror):
		    print "I/O error(%s): %s" % (errno, strerror)
	
	# Main method for indexing process	    
	def run(self):
		self.printClinNoteXML()
		self.printIndexingQueries()
